package com.lanou3g.demo;

import com.lanou3g.demo.bean.StudentBean;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ApacheDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/new_db";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);

        QueryRunner runner = new QueryRunner();
        String sql = "delete from t_score where stu_id = ? and course_id = ?";
        Object[] params = {1, 1};
//        runner.update(conn, sql, 1, 1);
        int num = runner.update(conn, sql, params);
        if (num > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }

//        DbUtils.close(conn);
//        DbUtils.commitAndClose(conn);

        // ----------查询--------------
        QueryRunner qr = new QueryRunner();
//        String querySQL = "select stu_id as stuId, stu_name as stuName, birthday, stu_gender as stuGender from t_students where stu_gender = ?";
        String querySQL = "select * from t_students where stu_gender = ?";
        Object[] p = {"男"};
//        RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
        List<StudentBean> beanList = qr.query(conn,
                querySQL,
                new BeanListHandler<StudentBean>(StudentBean.class, new UnderlineProcessor()),
                p);
        beanList.forEach(System.out::println);

//        ResultSetHandler
        // -------查询单条数据
        String singleSql = "select * from t_students where stu_id = ?";
        QueryRunner singleRunner = new QueryRunner();
        StudentBean stu = singleRunner.query(
                conn,
                singleSql,
                new BeanHandler<StudentBean>(StudentBean.class, new UnderlineProcessor()),
                11
        );
        System.out.println("--------------");
        System.out.println(stu);

        // {1, "赵雷", "199-9834-23", "男"}

        QueryRunner qr1 = new QueryRunner();
//        String querySQL = "select stu_id as stuId, stu_name as stuName, birthday, stu_gender as stuGender from t_students where stu_gender = ?";
        String q1 = "select * from t_students where stu_gender = ?";
        Object[] p1 = {"男"};
//        RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());

        List<Long> query = qr.query(conn,
                querySQL,
                new ColumnListHandler<Long>(1),
                p);
        System.out.println("------ColumnListHandler------");
        query.forEach(System.out::println);

        //
        QueryRunner qr2 = new QueryRunner();
//        String querySQL = "select stu_id as stuId, stu_name as stuName, birthday, stu_gender as stuGender from t_students where stu_gender = ?";
        String q2 = "select * from t_students where stu_gender = ?";
        Object[] p2 = {"男"};
//        RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());

        Map<Object, Map<String, Object>> map = qr2.query(conn,
                q2,
                new KeyedHandler<>(new UnderlineProcessor()),
                p2);
        System.out.println("------KeyedHandler------");
        System.out.println(map);

        DbUtils.commitAndCloseQuietly(conn);

    }
}
